package risk.export.record.poi;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 读取本地excel
 */
public class PoiTest {

    public static void main(String[] args) {
        //read();
        writ();

    }

    private static void writ() {
        String outputPath = "D:/output.xlsx";
        Workbook workbook = new SXSSFWorkbook(50000); // 创建XSSFWorkbook对象(excel2007+)
        Sheet sheet = workbook.createSheet("Sheet1");
        // 4. 创建表头样式
        CellStyle headerStyle = createHeaderStyle(workbook);
        // 写入数据
        for (int i = 0; i < 10000; i++) {
            Row row = sheet.createRow(i);
            sheet.setColumnWidth(i, 30 * 256);//固定列宽
            if (i == 0) {//创建表头
                row.setHeightInPoints(30);

                Cell cell = row.createCell(0);
                cell.setCellStyle(headerStyle);
                cell.setCellValue("这是表头1");

                row.createCell(1).setCellValue("这是表头2");
                row.createCell(2).setCellValue("这是表头3");
                row.createCell(3).setCellValue("这是表头4");
                row.createCell(4).setCellValue("这是表头5");
                row.createCell(5).setCellValue("这是表头6");
                row.createCell(6).setCellValue("这是表头7");
                row.createCell(7).setCellValue("这是表头8");
                row.createCell(8).setCellValue("这是表头9");
                row.createCell(9).setCellValue("这是表头10");
            } else {
                //内容
                row.createCell(0).setCellValue("这是数据1");
                row.createCell(1).setCellValue("这是数据2");
                row.createCell(2).setCellValue("这是数据3");
                row.createCell(3).setCellValue("这是数据4");
                row.createCell(4).setCellValue("这是数据5");
                row.createCell(5).setCellValue("这是数据6");
                row.createCell(6).setCellValue("这是数据7");
                row.createCell(7).setCellValue("这是数据8");
                row.createCell(8).setCellValue("这是数据9");
                row.createCell(9).setCellValue("这是数据10");
            }

        }
        // 写入文件
        try (FileOutputStream fos = new FileOutputStream(outputPath)) {
            workbook.write(fos);
            workbook.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    private static void read() {
        List<List<String>> data = new ArrayList<>();
        String filePath = "D:/output.xlsx";
        try (InputStream is = new FileInputStream(filePath); Workbook workbook = WorkbookFactory.create(is)) {
            Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
            int startRow = 1; // 从第2行开始读取(0-based)
            for (int i = startRow; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                List<String> rowData = new ArrayList<>();
                for (Cell cell : row) {
                    String cellValueAsString = getCellValueAsString(cell);
                    System.out.println(cellValueAsString);
                    rowData.add(cellValueAsString);
                }
                data.add(rowData);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    private static CellStyle createHeaderStyle(Workbook workbook) {
        // 1. 创建单元格样式
        CellStyle style = workbook.createCellStyle();

        // 2. 设置背景色（灰色）
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 3. 设置边框
//        style.setBorderBottom(BorderStyle.THIN);
//        style.setBorderTop(BorderStyle.THIN);
//        style.setBorderLeft(BorderStyle.THIN);
//        style.setBorderRight(BorderStyle.THIN);
//        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
//        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
//        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
//        style.setRightBorderColor(IndexedColors.BLACK.getIndex());

        // 4. 设置字体（加粗）
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontName("微软雅黑"); // 设置字体
        font.setFontHeightInPoints((short) 12); // 字号
        style.setFont(font);

        // 5. 设置对齐方式
        style.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        return style;
    }

    /**
     * 获取单元格的字符串值
     *
     * @param cell 单元格
     * @return 字符串形式的单元格值
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    double num = cell.getNumericCellValue();
                    // 判断是否为整数
                    if (num == (long) num) {
                        return String.valueOf((long) num); // 返回整数形式
                    }
                    return String.valueOf(num); // 非整数保持原样
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
            default:
                return "";
        }
    }
}
